package cn.edu.lingnan.dao;

import cn.edu.lingnan.pojo.Student;
import cn.edu.lingnan.util.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class IStudentDao implements StudentDao{

    @Override
    public Student studentLogin(int id, String password) {
        Student student = new Student();
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            conn = DBUtil.getInstance().getConnection();
            String sql = "SELECT * FROM students WHERE id = ? AND password = ?";
            ps = conn.prepareStatement(sql);
            ps.setInt(1, id);
            ps.setString(2, password);
            rs = ps.executeQuery();

            if (rs.next()) {
                student.setId(rs.getInt("id"));
                student.setName(rs.getString("name"));
                student.setGender(rs.getString("gender"));
                student.setMajor(rs.getString("major"));
                student.setClassX(rs.getString("class"));
                student.setRight(rs.getInt("right"));
                student.setFlag(rs.getInt("flag"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.getInstance().close(rs,ps,conn);
        }
        return student;
    }

    @Override
    public Student findStudentById(int id) {
        Student student = new Student();
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = DBUtil.getInstance().getConnection();
            ps = conn.prepareStatement("select * from students where id=?");
            ps.setInt(1,id);
            rs=ps.executeQuery();
            if(rs.next()){
                student.setId(rs.getInt("id"));
                student.setName(rs.getString("name"));
                student.setGender(rs.getString("gender"));
                student.setMajor(rs.getString("major"));
                student.setClassX(rs.getString("class"));
                student.setRight(rs.getInt("right"));
                student.setFlag(rs.getInt("flag"));
                //System.out.println(student);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        finally {
            DBUtil.getInstance().close(rs,ps,conn);
        }
        return student;
    }

    @Override
    public List<Student> findStudentAll() {
        List<Student> list = new ArrayList<>();
        Connection conn=null;
        PreparedStatement ps=null;
        ResultSet rs=null;
        try {
            conn=DBUtil.getInstance().getConnection();
            ps = conn.prepareStatement("SELECT * FROM students where `right`<>1");
            rs = ps.executeQuery();
            while (rs.next()){
                //找到一条记录，创建一个对应的学生对象，然后存入List
                Student student=new Student();
                student.setId(rs.getInt("id"));
                student.setName(rs.getString("name"));
                student.setPassword(rs.getString("password"));
                student.setGender(rs.getString("gender"));
                student.setMajor(rs.getString("major"));
                student.setClassX(rs.getString("class"));
                student.setRight(rs.getInt("right"));
                student.setFlag(rs.getInt("flag"));
                list.add(student);
            }
        }  catch (SQLException e) {
            throw new RuntimeException(e);
        }
        finally{
            DBUtil.getInstance().close(rs,ps,conn);
        }
        return list;
    }

    @Override
    public List<Student> getStudentByName(String _name) {
        List<Student> studentList = new ArrayList<>();
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            conn=DBUtil.getInstance().getConnection();
            String sql = "select * from students where name like concat('%',?,'%')";
            ps = conn.prepareStatement(sql);
            ps.setString(1,_name);
            rs = ps.executeQuery();
            while (rs.next()){
                Student student = new Student();
                student.setId(rs.getInt("id"));
                student.setName(rs.getString("name"));
                student.setGender(rs.getString("gender"));
                student.setMajor(rs.getString("major"));
                student.setClassX(rs.getString("class"));
                student.setRight(rs.getInt("right"));
                student.setFlag(rs.getInt("flag"));
                studentList.add(student);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            //关闭资源 先开的后关
            DBUtil.getInstance().close(rs,ps,conn);
        }
        return studentList;
    }

    @Override
    public int addStudent(Student student) {
            int result = 0;
            Connection conn = null;
            PreparedStatement ps = null;
            try {
                conn = DBUtil.getInstance().getConnection();
                ps = conn.prepareStatement("insert into students (id,name,password, gender, major,class,`right`,flag) values (?,?,?,?,?,?,?,?)");
                ps.setInt(1,student.getId());
                ps.setString(2, student.getName());
                ps.setString(3,student.getPassword());
                ps.setString(4, student.getGender());
                ps.setString(5, student.getMajor());
                ps.setString(6, student.getClassX());
                ps.setInt(7,student.getRight());
                ps.setInt(8,student.getRight());
                ps.executeUpdate();
                result=1;

            } catch (SQLException e) {
                throw new RuntimeException(e);
            } finally {
                DBUtil.getInstance().close(null,ps, conn);
            }
            return result;
    }

    @Override
    public int delStudent(int id) {
        int flag = 0;
        try (Connection conn = DBUtil.getInstance().getConnection();
             PreparedStatement ps = conn.prepareStatement("delete from students where id=?")) {
            ps.setInt(1, id);
            int rowsAffected = ps.executeUpdate();
            if (rowsAffected > 0) {
                flag = 1; // 删除成功
            } else {
                flag = 0; // 删除失败
            }
        } catch (SQLException e) {
            return 2;//出现异常请检查竞赛记录表里是否有记录存在
        }
        return flag;
    }



    @Override
    public int updateStudent(Student student) {
        int flag = 0;
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = DBUtil.getInstance().getConnection();
                ps = conn.prepareStatement("update students set name=?,password=?,gender=?,major=?,class=?,`right`=?,flag=? where id=?");
                ps.setString(1, student.getName());
                ps.setString(2, student.getPassword());
                ps.setString(3, student.getGender());
                ps.setString(4, student.getMajor());
                ps.setString(5,student.getClassX());
                ps.setInt(6,student.getRight());
                ps.setInt(7,student.getFlag());
                ps.setInt(8,student.getId());
                int rowsAffected = ps.executeUpdate();
                if (rowsAffected > 0) {
                    flag = 1; // 删除成功
                } else {
                    flag = 0; // 删除失败
                }

        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DBUtil.getInstance().close(null, ps, conn);
        }
        return flag;
    }

    @Override
    public int updateStuFlag(int sid) {
        int flag = 0;
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = DBUtil.getInstance().getConnection();
            ps = conn.prepareStatement("update students set flag=? where id=?");
            ps.setInt(1,1);
            ps.setInt(2,sid);
            int rowsAffected = ps.executeUpdate();
            if (rowsAffected > 0) {
                flag = 1; // 删除成功
            } else {
                flag = 0; // 删除失败
            }

        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DBUtil.getInstance().close(null, ps, conn);
        }
        return flag;
    }

    public boolean checkPassword(int id, String oldPassword) {
        try (Connection conn = DBUtil.getInstance().getConnection();
             PreparedStatement stmt = conn.prepareStatement("SELECT password FROM students WHERE id = ?")) {
            stmt.setInt(1, id);
            ResultSet rs = stmt.executeQuery();
            if (rs.next() && rs.getString("password").equals(oldPassword)) {
                return true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    public boolean updatePassword(int id, String newPassword) {
        try (Connection conn = DBUtil.getInstance().getConnection();
             PreparedStatement stmt = conn.prepareStatement("UPDATE students SET password = ? WHERE id = ?")) {
            stmt.setString(1, newPassword);
            stmt.setInt(2, id);
            int rows = stmt.executeUpdate();
            return rows > 0;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    @Override
    public boolean resetPassword(String userId) {
        Connection conn = null;
        PreparedStatement stmt = null;
        try {
            conn =DBUtil.getInstance().getConnection();
            // 执行更新语句
            String sql = "UPDATE students SET password = ? WHERE id = ?";
            stmt = conn.prepareStatement(sql);
            // 设置参数
            stmt.setString(1, "linnan" + userId.substring(userId.length() - 4)); // 设置为默认密码
            stmt.setString(2, userId);
            // 执行更新
            int rowsUpdated = stmt.executeUpdate();
            if (rowsUpdated > 0) {
                System.out.println("密码重置成功！");
                return true;
            } else {
                System.out.println("未找到用户ID：" + userId);
            }
        } catch (SQLException se) {
            se.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.getInstance().close(null, stmt, conn);
        }
        return false;
    }
}
